package com.mbc.rmt.manager;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JRResultSetDataSource;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperReport;
import net.sf.jasperreports.engine.util.JRLoader;

import com.mbc.common.CommonSingleton;
import com.mbc.common.dao.CommonDAOFactory;
import com.mbc.common.dao.CurrencyRateDAO;
import com.mbc.common.dao.LogDAO;
import com.mbc.common.entities.Branch;
import com.mbc.common.entities.CurrencyRate;
import com.mbc.common.entities.Log;
import com.mbc.common.exception.DataNotFoundException;
import com.mbc.common.manager.AbstractMgr;
import com.mbc.common.param.CriteriaParams;
import com.mbc.common.utils.KeyGenerator;
import com.mbc.excel.generate.GenerateExcel;
import com.mbc.rmt.dao.AgentBalanceDAO;
import com.mbc.rmt.dao.AgentCOADAO;
import com.mbc.rmt.dao.AgentDAO;
import com.mbc.rmt.dao.BranchCOADAO;
import com.mbc.rmt.dao.CashRemittanceByAgentDAO;
import com.mbc.rmt.dao.DefinePaymentTypeDAO;
import com.mbc.rmt.dao.DefinePaymentTypeDAOImpls;
import com.mbc.rmt.dao.DefinePaymentTypeDetailDAO;
import com.mbc.rmt.dao.OfficeCOADAO;
import com.mbc.rmt.dao.RemittanceCashReceivedArchiveDAO;
import com.mbc.rmt.dao.RemittanceDAOFactory;
import com.mbc.rmt.dao.SentCOADAO;
import com.mbc.rmt.dao.WaitingCOADAO;
import com.mbc.rmt.data.ConfirmDataList;
import com.mbc.rmt.data.DefinePaymentTypeDataList;
import com.mbc.rmt.data.DefinePaymentTypeDetailDataList;
import com.mbc.rmt.entities.Agent;
import com.mbc.rmt.entities.AgentBalance;
import com.mbc.rmt.entities.AgentCOA;
import com.mbc.rmt.entities.BranchCOA;
import com.mbc.rmt.entities.CashRemittanceByAgent;
import com.mbc.rmt.entities.DefinePaymentPrint;
import com.mbc.rmt.entities.DefinePaymentType;
import com.mbc.rmt.entities.DefinePaymentTypeDetail;
import com.mbc.rmt.entities.DefinePaymentTypeRpt;
import com.mbc.rmt.entities.GeneralLedger;
import com.mbc.rmt.entities.OfficeCOA;
import com.mbc.rmt.entities.SentCOA;
import com.mbc.rmt.entities.WaitingCOA;

public class DefinePaymentTypeMgr extends AbstractMgr {
	
	private DefinePaymentTypeDAO definePaymentTypeDAO;
	private DefinePaymentTypeDetailDAO definePaymentTypeDetailDAO;
	
	private CashRemittanceByAgentDAO cashRemittanceByAgentDAO;
	private RemittanceCashReceivedArchiveDAO remittanceCashReceivedArchiveDAO;
	
	private AgentBalanceDAO agentBalanceDAO;
	private BranchCOADAO branchCOADAO;
	private CurrencyRateDAO currencyRateDAO;
	private WaitingCOADAO waitingCOADAO;
	private OfficeCOADAO officeCOADAO;
	private SentCOADAO sendCOADAO;
	private AgentCOADAO agentCOADAO;
	private AgentDAO agentDAO;
	
	private LogDAO logDAO;
	
	public static enum GL_STATUS { CREATE, DELETE }
	public static enum TRAN_TYPE { SIMPLE, DETAIL }
	
	public DefinePaymentTypeMgr(){
		
	}
	public DefinePaymentTypeMgr(String sessionID){
		this.sessionId = sessionID;
	}
	public String create(CashRemittanceByAgent cofirmdata,DefinePaymentType entity)throws Exception {
		openConnection();
		try{
			beginTransaction();
			cashRemittanceByAgentDAO = RemittanceDAOFactory.getCashRemittanceByAgentDAO(getConnection());
			definePaymentTypeDAO = RemittanceDAOFactory.getDefinePaymentTypeDAO(getConnection());
			definePaymentTypeDetailDAO = RemittanceDAOFactory.getDefinePaymentTypeDetailDAO(getConnection());
			agentBalanceDAO = RemittanceDAOFactory.getAgentBalanceDAO(getConnection());
			
			logDAO = CommonDAOFactory.getLogDAO(getConnection());
			
			for(int i = 0; i < cofirmdata.getCrByAgentDetailList().size(); i++) {
				if(cofirmdata.getCrByAgentDetailList().get(i).getReceiptDetailId() != 0)
					cashRemittanceByAgentDAO.updateStatus(cofirmdata.getCrByAgentDetailList().get(i).getStatus(), cofirmdata.getCrByAgentDetailList().get(i).getReceiptDetailId());
				else
					definePaymentTypeDAO.updateStatus((byte)1, cofirmdata.getCrByAgentDetailList().get(i).getReceiptId());
			}
			
			String voucher = RemittanceSerialMgr.getFormattedSerial(RemittanceSerialMgr.DEFINE_PAYMENT_TYPE, entity.getInvoiceDate(), entity.getBranchId(), getConnection());
			
			entity.setInvoiceId(KeyGenerator.generateKey(getConnection()));
			entity.setInvoiceCode(voucher);
			
			gotoGL(entity, GL_STATUS.CREATE, TRAN_TYPE.SIMPLE);
			
			definePaymentTypeDAO.create(entity);
			
			Log logentity = new Log();
			logentity.setTablename("DefinePaymentType");
			logentity.setLogkey(KeyGenerator.generateKey(getConnection()));
			logentity.setKey(entity.getInvoiceId());
			logentity.setUserid(entity.getCreatedBy());
			logentity.setLogstatus(Log.__SAVE_DATA);
			logDAO.getData(logentity);
			
			for(int j = 0; j < entity.getL_detail().size(); j++) {
				
				entity.getL_detail().get(j).setInvoiceId(entity.getInvoiceId());
				entity.getL_detail().get(j).setInvoiceCode(entity.getInvoiceCode());
				entity.getL_detail().get(j).setDetailId(KeyGenerator.generateKey(getConnection()));
				entity.getL_detail().get(j).setCreatedBy(entity.getCreatedBy());
				
				/*if(!entity.getL_detail().get(j).getTypeCode().equals("A") || !entity.getL_detail().get(j).getTypeCode().equals("B")) {
					AgentBalance l_agentbalance = new AgentBalance();
					l_agentbalance.setTypestatus(entity.getL_detail().get(j).getTypeCode());
					l_agentbalance.setCurrencyid(entity.getL_detail().get(j).getCurrencyId());
					
					AgentBalance l_oldagentbalance = agentBalanceDAO.getInfo(l_agentbalance);
					
					if(l_oldagentbalance != null) {
						l_oldagentbalance.setCurrentbalance(l_oldagentbalance.getCurrentbalance() - entity.getL_detail().get(j).getAmount());
						agentBalanceDAO.update(l_oldagentbalance);
					} else {
						l_agentbalance.setAgentbalance_id(KeyGenerator.generateKey(getConnection()));
						l_agentbalance.setAgent_id(0);
						l_agentbalance.setCurrentbalance(-entity.getL_detail().get(j).getAmount());
						agentBalanceDAO.create(l_agentbalance);
					}
				}*/
				
				definePaymentTypeDetailDAO.create(entity.getL_detail().get(j));
				
				logentity.setLogkey(KeyGenerator.generateKey(getConnection()));
				logentity.setTablename("DefinePaymentTypeDetail");
				logentity.setKey(entity.getL_detail().get(j).getDetailId());
				logDAO.getData(logentity);
			}
			
			commintTransaction();
			
			return voucher;
		}catch(Exception e){
			rollbackTransaction();
				throw e;
		}finally{
			closeConnection();
		}
	}
	public void createDetail(DefinePaymentTypeDetail entity)throws Exception{
		openConnection();
		try{
			beginTransaction();
			commintTransaction();
		}catch(Exception e){
			throw e;
		}finally {
			closeConnection();
		}
	}
	public void update(DefinePaymentType entity) throws Exception{
		openConnection();
		try{
			beginTransaction();
			
			definePaymentTypeDAO = RemittanceDAOFactory.getDefinePaymentTypeDAO(getConnection());
			definePaymentTypeDetailDAO = RemittanceDAOFactory.getDefinePaymentTypeDetailDAO(getConnection());
			agentBalanceDAO = RemittanceDAOFactory.getAgentBalanceDAO(getConnection());
			
			logDAO = CommonDAOFactory.getLogDAO(getConnection());
			
			definePaymentTypeDAO.update(entity);
			
			Log logentity = new Log();
			logentity.setTablename("DefinePaymentType");
			logentity.setLogkey(KeyGenerator.generateKey(getConnection()));
			logentity.setKey(entity.getInvoiceId());
			logentity.setUserid(entity.getModifiedBy());
			logentity.setLogstatus(Log.__UPDATE_DATA);
			logDAO.getData(logentity);
			
			for(int j = 0; j < entity.getL_detail().size(); j++) {
				entity.getL_detail().get(j).setInvoiceId(entity.getInvoiceId());
				entity.getL_detail().get(j).setInvoiceCode(entity.getInvoiceCode());
				entity.getL_detail().get(j).setModifiedBy(entity.getModifiedBy());
				
				/*DefinePaymentTypeDetail l_detail = definePaymentTypeDetailDAO.findById(entity.getL_detail().get(j).getDetailId());
				DefinePaymentTypeDetail l_current = entity.getL_detail().get(j);
				
				long oldcurrency = l_detail.getCurrencyId();
				long newcurrency = l_current.getCurrencyId();
				
				String oldtypecode = l_detail.getTypeCode();
				String newtypecode = l_current.getTypeCode();
				
				if(oldcurrency != newcurrency || !oldtypecode.equals(newtypecode)) {
					
					AgentBalance l_tagentbalance = new AgentBalance();
					l_tagentbalance.setTypestatus(l_detail.getTypeCode());
					l_tagentbalance.setCurrencyid(l_detail.getCurrencyId());
					
					l_tagentbalance = agentBalanceDAO.getInfo(l_tagentbalance);
					if(l_tagentbalance != null) {
						l_tagentbalance.setCurrentbalance(l_tagentbalance.getCurrentbalance() + l_detail.getAmount());
						agentBalanceDAO.update(l_tagentbalance);
					}
					
					if(!newtypecode.equals("A") || !newtypecode.equals("B")) {
						AgentBalance l_agentbalance = new AgentBalance();
						l_agentbalance.setTypestatus(entity.getL_detail().get(j).getTypeCode());
						l_agentbalance.setCurrencyid(entity.getL_detail().get(j).getCurrencyId());
						
						AgentBalance l_oldagentbalance = agentBalanceDAO.getInfo(l_agentbalance);
						
						if(l_oldagentbalance != null) {
							l_oldagentbalance.setCurrentbalance(l_agentbalance.getCurrentbalance() - entity.getL_detail().get(j).getAmount());
							agentBalanceDAO.update(l_oldagentbalance);
						} else {
							l_agentbalance.setAgentbalance_id(KeyGenerator.generateKey(getConnection()));
							l_agentbalance.setAgent_id(0);
							l_agentbalance.setCurrentbalance(-entity.getL_detail().get(j).getAmount());
							agentBalanceDAO.create(l_agentbalance);
						}
					}
				}*/
				
				definePaymentTypeDetailDAO.update(entity.getL_detail().get(j));
				
				logentity.setTablename("DefinePaymentTypeDetail");
				logentity.setLogkey(KeyGenerator.generateKey(getConnection()));
				logentity.setKey(entity.getL_detail().get(j).getDetailId());
				logDAO.getData(logentity);
			}
			
			gotoGL(entity, GL_STATUS.CREATE, TRAN_TYPE.SIMPLE);
			
			commintTransaction();
		}catch(Exception e){
			rollbackTransaction();
				throw e;
		}finally{
			closeConnection();
		}
	}
	public void delete (DefinePaymentType l_info)throws Exception{
		openConnection();
		try{
			beginTransaction();
			
			cashRemittanceByAgentDAO = RemittanceDAOFactory.getCashRemittanceByAgentDAO(getConnection());
			definePaymentTypeDAO = RemittanceDAOFactory.getDefinePaymentTypeDAO(getConnection());
			definePaymentTypeDetailDAO = RemittanceDAOFactory.getDefinePaymentTypeDetailDAO(getConnection());
			agentBalanceDAO = RemittanceDAOFactory.getAgentBalanceDAO(getConnection());
			
			logDAO = CommonDAOFactory.getLogDAO(getConnection());
			
			for(int i = 0; i < l_info.getL_detail().size(); i++) {
				
				if(l_info.getL_detail().get(i).getReceiptDetailId() != 0)
					cashRemittanceByAgentDAO.updateStatus(null, l_info.getL_detail().get(i).getReceiptDetailId());
				else
					definePaymentTypeDAO.updateStatus(null, l_info.getL_detail().get(i).getReceiptId());
			}
			
			/*ArrayList<DefinePaymentTypeDetail> l_detail = definePaymentTypeDetailDAO.findByInvoiceId(l_info.getInvoiceId());
			for(int j = 0; j < l_detail.size(); j++) {
				if(!l_detail.get(j).getTypeCode().equals("A") || !l_detail.get(j).getTypeCode().equals("B")) {
					AgentBalance l_agentbalance = new AgentBalance();
					l_agentbalance.setTypestatus(l_detail.get(j).getTypeCode());
					l_agentbalance.setCurrencyid(l_detail.get(j).getCurrencyId());
					
					AgentBalance l_oldagentbalance = agentBalanceDAO.getInfo(l_agentbalance);
					
					if(l_oldagentbalance != null) {
						l_oldagentbalance.setCurrentbalance(l_oldagentbalance.getCurrentbalance() + l_detail.get(j).getAmount());
						agentBalanceDAO.update(l_oldagentbalance);
					}
				}
			}*/
			
			DefinePaymentType l_entity = definePaymentTypeDAO.findById(l_info.getInvoiceId());
			l_entity.setL_detail(definePaymentTypeDetailDAO.findByInvoiceId(l_info.getInvoiceId()));
			
			gotoGL(l_entity, GL_STATUS.DELETE, TRAN_TYPE.SIMPLE);
			
			Log logentity = new Log();
			logentity.setTablename("DefinePaymentType");
			logentity.setLogkey(KeyGenerator.generateKey(getConnection()));
			logentity.setKey(l_entity.getInvoiceId());
			logentity.setUserid(l_entity.getModifiedBy());
			logentity.setLogstatus(Log.__DELETE_DATA);
			logDAO.getData(logentity);
			
			ArrayList<DefinePaymentTypeDetail> l_detail = definePaymentTypeDetailDAO.findByInvoiceId(l_info.getInvoiceId());
			for(int j = 0; j < l_detail.size(); j++) {
				logentity.setTablename("DefinePaymentTypeDetail");
				logentity.setLogkey(KeyGenerator.generateKey(getConnection()));
				logentity.setKey(l_detail.get(j).getDetailId());
				logentity.setLogstatus(Log.__DELETE_DATA);
				logDAO.getData(logentity);
			}
			
			definePaymentTypeDetailDAO.deleteByInvoiceId(l_info.getInvoiceId());
			definePaymentTypeDAO.delete(l_info.getInvoiceId());
			
			commintTransaction();
		}catch(Exception e){
			rollbackTransaction();
			throw e;
		}finally {
			closeConnection();
		}
	}
	
	public void rollbackAgent(ArrayList<Long> detailids)throws Exception {
		openConnection();
		try {
			beginTransaction();
			
			agentBalanceDAO = RemittanceDAOFactory.getAgentBalanceDAO(getConnection());
			definePaymentTypeDetailDAO = RemittanceDAOFactory.getDefinePaymentTypeDetailDAO(getConnection());
			
			for(int i = 0; i < detailids.size(); i++) {
				DefinePaymentTypeDetail l_info = definePaymentTypeDetailDAO.findById(detailids.get(i));
				
				if(l_info != null) {
					AgentBalance l_agentbalance = agentBalanceDAO.find(l_info.getAgentId());
					if(l_agentbalance != null) {
						l_agentbalance.setCurrentbalance(l_agentbalance.getCurrentbalance() + l_info.getAmount());
						agentBalanceDAO.update(l_agentbalance);
					}
				}
				
				DefinePaymentType l_temp = new DefinePaymentType();
				l_temp.setInvoiceCode(definePaymentTypeDetailDAO.findById(detailids.get(i)).getSlip_No());
				
				gotoGL(l_temp, GL_STATUS.DELETE, TRAN_TYPE.DETAIL);
				
				definePaymentTypeDetailDAO.rollbackAgent(detailids.get(i));
			}
			
			commintTransaction();
		} catch(Exception e) {
			rollbackTransaction();
			throw e;
		} finally {
			closeConnection();
		}
	}
	
	public DefinePaymentTypeDataList findDefinePaymentType(CriteriaParams cri)throws Exception{
		openConnection();
		try{
			
			return null;
		}catch(Exception e){
			rollbackTransaction();
			throw e;
		}finally{
			closeConnection();
		}
	}
	public DefinePaymentType find(String invoiceNo)throws Exception{
		openConnection();
		try {
			return null;
		} catch (Exception e) {
			throw e;
		} finally {
			closeConnection();
		}
	}
	
	public DefinePaymentType findDefinePaymentTypeDetails(
			long ReceiptId) throws Exception {
		openConnection();
		try {
			return null;
		} catch (Exception e) {
			throw e;
		} finally {
			closeConnection();
		}
	}
	
	public ConfirmDataList getConfirmData(Branch l_entity, long defaultBranch) throws Exception {
		openConnection();
		try {
			ConfirmDataList c = new ConfirmDataList();
			
			definePaymentTypeDetailDAO = RemittanceDAOFactory.getDefinePaymentTypeDetailDAO(getConnection());
			remittanceCashReceivedArchiveDAO = RemittanceDAOFactory.getRemittanceCashReceivedArchiveDAO(getConnection());
			
			c.setEntityTList(remittanceCashReceivedArchiveDAO.findConfirmAll(l_entity.getBranchId(), defaultBranch));
			c.setEntityVList(definePaymentTypeDetailDAO.getConfirmData(l_entity, defaultBranch).getEntityList());
			
			return c;
		} catch(Exception ex) {
			throw ex;
		} finally {
			closeConnection();
		}
	}
	
	public DefinePaymentTypeDataList getAllDefinePaymentType(DefinePaymentType param)throws Exception {
		openConnection();
		try {
			definePaymentTypeDAO = RemittanceDAOFactory.getDefinePaymentTypeDAO(getConnection());
			definePaymentTypeDetailDAO = RemittanceDAOFactory.getDefinePaymentTypeDetailDAO(getConnection());
			
			if(param.isAddressLookup()) {
				
				return definePaymentTypeDetailDAO.findDetail(param);
				
			} else {
				
				DefinePaymentTypeDataList l_dataList = definePaymentTypeDAO.findDPTypeList(param);
				
				for(int i = 0; i < l_dataList.getEntityList().size(); i++) {
					ArrayList<DefinePaymentTypeDetail> l_list = definePaymentTypeDetailDAO.findByInvoiceId(l_dataList.getEntityList().get(i).getInvoiceId());
					l_dataList.getEntityList().get(i).setL_detail(l_list);
				}
				return l_dataList;
				
			}
		} catch(Exception e) {
			throw e;
		} finally {
			closeConnection();
		}
	}
	
	public DefinePaymentTypeDetailDataList getDPDbyANBList(String typecode, DefinePaymentType entity)throws Exception {
		openConnection();
		try {
			definePaymentTypeDetailDAO = RemittanceDAOFactory.getDefinePaymentTypeDetailDAO(getConnection());
			return definePaymentTypeDetailDAO.getDPDbyANBList(typecode, entity);
		} catch(Exception e) {
			throw e;
		} finally {
			closeConnection();
		}
	}
	
	public DefinePaymentTypeDetailDataList getInvoiceNo(DefinePaymentTypeDetail param) throws Exception {
		openConnection();
		try {
			definePaymentTypeDetailDAO = RemittanceDAOFactory.getDefinePaymentTypeDetailDAO(getConnection());
			return definePaymentTypeDetailDAO.getInvoiceNo(param);
		} catch(Exception e) {
			throw e;
		} finally {
			closeConnection();
		}
	}
	
	public String updateAgent(ArrayList<DefinePaymentTypeDetail> entity, long branchID) throws Exception {
		openConnection();
		try {
			beginTransaction();
			definePaymentTypeDAO = RemittanceDAOFactory.getDefinePaymentTypeDAO(getConnection());
			definePaymentTypeDetailDAO = RemittanceDAOFactory.getDefinePaymentTypeDetailDAO(getConnection());
			agentBalanceDAO = RemittanceDAOFactory.getAgentBalanceDAO(getConnection());
			
			String newSlip = RemittanceSerialMgr.getFormattedSerial(RemittanceSerialMgr.DEFINE_PAYMENT_DETAILS_BY_AGENT_N_BANK, (new Date()), branchID, getConnection());
			
			ArrayList<DefinePaymentTypeDetail> l_tempdetail = new ArrayList<DefinePaymentTypeDetail>();
			
			for(int i = 0; i < entity.size(); i++) {
				if(entity.get(i).getSlip_No().equals("")) {
					entity.get(i).setSlip_No(newSlip);
					
					AgentBalance l_abalance = new AgentBalance();
					l_abalance.setAgent_id(entity.get(i).getAgentId());
					l_abalance.setCurrencyid(entity.get(i).getCurrencyId());
					l_abalance = agentBalanceDAO.getInfo(l_abalance);
					
					if(l_abalance == null) {
						AgentBalance l_Nabalance = new AgentBalance();
						l_Nabalance.setAgentbalance_id(KeyGenerator.generateKey(getConnection()));
						l_Nabalance.setAgent_id(entity.get(i).getAgentId());
						l_Nabalance.setCurrencyid(entity.get(i).getCurrencyId());
						l_Nabalance.setCurrentbalance(-entity.get(i).getAmount());
						agentBalanceDAO.create(l_Nabalance);
					} else {
						l_abalance.setCurrentbalance(l_abalance.getCurrentbalance() - entity.get(i).getAmount());
						agentBalanceDAO.update(l_abalance);
					}
				} else {
					
					DefinePaymentTypeDetail l_detail = definePaymentTypeDetailDAO.findById(entity.get(i).getDetailId());
					if(l_detail.getAgentId() != entity.get(i).getAgentId()) {
						AgentBalance l_abalance = new AgentBalance();
						l_abalance.setAgent_id(entity.get(i).getAgentId());
						l_abalance.setCurrencyid(entity.get(i).getCurrencyId());
						l_abalance = agentBalanceDAO.getInfo(l_abalance);
						l_abalance.setCurrentbalance(l_abalance.getCurrentbalance() - entity.get(i).getAmount());
						agentBalanceDAO.update(l_abalance);
						
						AgentBalance l_Nabalance = new AgentBalance();
						l_Nabalance.setAgent_id(l_detail.getAgentId());
						l_Nabalance.setCurrencyid(l_detail.getCurrencyId());
						l_Nabalance = agentBalanceDAO.getInfo(l_Nabalance);
						l_abalance.setCurrentbalance(l_abalance.getCurrentbalance() + l_detail.getAmount());
						agentBalanceDAO.update(l_Nabalance);
					}
					
				}
				
				DefinePaymentTypeDetail _entity = definePaymentTypeDetailDAO.findById(entity.get(i).getDetailId());
				_entity.setAgentId(entity.get(i).getAgentId());
				_entity.setSlip_No(entity.get(i).getSlip_No());
				_entity.setInvoiceDate(entity.get(i).getInvoiceDate());
				
				l_tempdetail.add(_entity);
				
				definePaymentTypeDetailDAO.updateAgent(entity.get(i));
			}
			
			DefinePaymentType l_temp = definePaymentTypeDAO.findById(definePaymentTypeDetailDAO.findById(entity.get(0).getDetailId()).getInvoiceId());
			l_temp.setInvoiceCode(entity.get(0).getSlip_No());
			l_temp.setInvoiceDate(entity.get(0).getInvoiceDate());
			l_temp.setL_detail(l_tempdetail);
			
			gotoGL(l_temp, GL_STATUS.CREATE, TRAN_TYPE.DETAIL);
			
			commintTransaction();
			
			return newSlip;
		} catch(Exception e) {
			rollbackTransaction();
			throw e;
		} finally {
			closeConnection();
		}
	}
	
	public DefinePaymentTypeDetailDataList getDPDbyANBList(DefinePaymentTypeDetail entity) throws Exception {
		openConnection();
		try {
			definePaymentTypeDetailDAO = RemittanceDAOFactory.getDefinePaymentTypeDetailDAO(getConnection());
			
			return definePaymentTypeDetailDAO.getDPDbyANBList(entity);
		} catch(Exception e) {
			throw e;
		} finally {
			closeConnection();
		}
	}
	
	public void gotoGL(DefinePaymentType l_entity, GL_STATUS status, TRAN_TYPE trtype) throws Exception {
		
		currencyRateDAO = CommonDAOFactory.getCurrencyRateDAO(getConnection());
		branchCOADAO = RemittanceDAOFactory.getBranchCOADAO(getConnection());
		officeCOADAO = RemittanceDAOFactory.getOfficeCOADAO(getConnection());
		waitingCOADAO = RemittanceDAOFactory.getWaitingCOADAO(getConnection());
		sendCOADAO = RemittanceDAOFactory.getSentCOADAO(getConnection());
		agentDAO = RemittanceDAOFactory.getAgentDAO(getConnection());
		agentCOADAO = RemittanceDAOFactory.getAgentCOADAO(getConnection());
		
		GeneralLedgerMgr l_glmgr = new GeneralLedgerMgr(this.sessionId);
		
		if(trtype == TRAN_TYPE.SIMPLE)
			l_glmgr.deleteGL(l_entity.getInvoiceCode() + "-DP");
		else if(trtype == TRAN_TYPE.DETAIL)
			l_glmgr.deleteGL(l_entity.getInvoiceCode() + "-DD");
		
		if(status == GL_STATUS.CREATE && trtype == TRAN_TYPE.SIMPLE) {
			
			BranchCOA l_branchCOA = branchCOADAO.findByHostBranchID(l_entity.getBranchId(), l_entity.getFromBranchId());
			
			if(l_branchCOA == null)
				throw new DataNotFoundException("Plz fill BranchCOA", BranchCOA.class);
			
			GeneralLedger _glHeader = new GeneralLedger();
			_glHeader.setGlKey(l_glmgr.getGLKey());
			_glHeader.setSlip_ID(l_entity.getInvoiceCode() + "-DP");
			_glHeader.setSlipDate(l_entity.getInvoiceDate());
			_glHeader.setSystemDate(new Date());
			_glHeader.setStatus("JH");
			_glHeader.setSrNo(1);
			_glHeader.setOffice_ID(l_entity.getBranchId());
			
			l_glmgr.createGL(_glHeader);
			
			for(int i = 0; i < l_entity.getL_detail().size(); i++) {
				
				if(!l_entity.getL_detail().get(i).getTypeCode().equals("A") && !l_entity.getL_detail().get(i).getTypeCode().equals("B")) {
				
					CurrencyRate l_currencyrate = currencyRateDAO.getCurrencyRateFromSourcetoHome(l_entity.getL_detail().get(i).getCurrencyId(), l_entity.getBranchId());
					
					GeneralLedger _glDebit = new GeneralLedger();
					_glDebit.setGlKey(l_glmgr.getGLKey());
					_glDebit.setSlip_ID(l_entity.getInvoiceCode() + "-DP");
					_glDebit.setSlipDate(l_entity.getInvoiceDate());
					_glDebit.setSystemDate(new Date());
					_glDebit.setCoaKey(l_branchCOA.getCurrentAccountID());
					_glDebit.setDepartmentKey(l_branchCOA.getDepartmentID());
					_glDebit.setCurrencyKey(l_entity.getL_detail().get(i).getCurrencyId());
					_glDebit.setCurrencyRateKey(l_currencyrate.getCurrencyRateId());
					_glDebit.setExchangeRate(l_currencyrate.getRate());
					_glDebit.setSourceAmount(l_entity.getL_detail().get(i).getAmount());
					
					if(l_currencyrate.getRoper().equals("M"))
						_glDebit.setHomeAmount((l_entity.getL_detail().get(i).getAmount() * l_currencyrate.getRate()));
					else
						_glDebit.setHomeAmount((l_entity.getL_detail().get(i).getAmount() / l_currencyrate.getRate()));
					
					_glDebit.setStatus("JDP");
					_glDebit.setSrNo((i + 1));
					_glDebit.setOffice_ID(l_entity.getBranchId());
					
					l_glmgr.createGL(_glDebit);
					
					GeneralLedger _glCredit = new GeneralLedger();
					_glCredit.setGlKey(l_glmgr.getGLKey());
					_glCredit.setSlip_ID(l_entity.getInvoiceCode() + "-DP");
					_glCredit.setSlipDate(l_entity.getInvoiceDate());
					_glCredit.setSystemDate(new Date());
					
					if(l_entity.getL_detail().get(i).getTypeCode().equals("O")) {
						OfficeCOA l_officecoa = officeCOADAO.findByCode("O", l_entity.getBranchId());
						
						if(l_officecoa == null)
							throw new DataNotFoundException("Plz fill Office COA", OfficeCOA.class);
						
						_glCredit.setCoaKey(l_officecoa.getAccpayable());
						_glCredit.setDepartmentKey(l_officecoa.getAccpayable_dept());
					} else if(l_entity.getL_detail().get(i).getTypeCode().equals("W")) {
						WaitingCOA l_waitingcoa = waitingCOADAO.findByCode("W", l_entity.getBranchId());
						
						if(l_waitingcoa == null)
							throw new DataNotFoundException("Plz fill Waiting COA", WaitingCOA.class);
						
						_glCredit.setCoaKey(l_waitingcoa.getAccpayable());
						_glCredit.setDepartmentKey(l_waitingcoa.getAccpayable_dept());
					} else if(l_entity.getL_detail().get(i).getTypeCode().equals("S")) {
						SentCOA l_sendcoa = sendCOADAO.findByCode("S", l_entity.getBranchId());
						
						if(l_sendcoa == null)
							throw new DataNotFoundException("Plz fill Send COA", SentCOA.class);
						
						_glCredit.setCoaKey(l_sendcoa.getAccpayable());
						_glCredit.setDepartmentKey(l_sendcoa.getAccpayable_dept());
					}
					
					_glCredit.setCurrencyKey(l_entity.getL_detail().get(i).getCurrencyId());
					_glCredit.setCurrencyRateKey(l_currencyrate.getCurrencyRateId());
					_glCredit.setExchangeRate(l_currencyrate.getRate());
					_glCredit.setSourceAmount(-(l_entity.getL_detail().get(i).getAmount()));
					
					if(l_currencyrate.getRoper().equals("M"))
						_glCredit.setHomeAmount(-(l_entity.getL_detail().get(i).getAmount() * l_currencyrate.getRate()));
					else
						_glCredit.setHomeAmount(-(l_entity.getL_detail().get(i).getAmount() / l_currencyrate.getRate()));
					
					_glCredit.setStatus("JDP");
					_glCredit.setSrNo((i + 1));
					_glCredit.setOffice_ID(l_entity.getBranchId());
					
					l_glmgr.createGL(_glCredit);
				}
			}
		 
		} else if(status == GL_STATUS.CREATE && trtype == TRAN_TYPE.DETAIL) {
			
			BranchCOA l_branchCOA = branchCOADAO.findByHostBranchID(l_entity.getBranchId(), l_entity.getFromBranchId());
			
			if(l_branchCOA == null)
				throw new DataNotFoundException("Plz fill BranchCOA", BranchCOA.class);
			
			GeneralLedger _glHeader = new GeneralLedger();
			_glHeader.setGlKey(l_glmgr.getGLKey());
			_glHeader.setSlip_ID(l_entity.getInvoiceCode() + "-DD");
			_glHeader.setSlipDate(l_entity.getInvoiceDate());
			_glHeader.setSystemDate(new Date());
			_glHeader.setStatus("JH");
			_glHeader.setSrNo(1);
			_glHeader.setOffice_ID(l_entity.getBranchId());
			
			l_glmgr.createGL(_glHeader);
			
			for(int i = 0; i < l_entity.getL_detail().size(); i++) {
				
				if(l_entity.getL_detail().get(i).getTypeCode().equals("A") || l_entity.getL_detail().get(i).getTypeCode().equals("B")) {
				
					CurrencyRate l_currencyrate = currencyRateDAO.getCurrencyRateFromSourcetoHome(l_entity.getL_detail().get(i).getCurrencyId(), l_entity.getBranchId());
					
					Agent l_agent = agentDAO.findById(l_entity.getL_detail().get(i).getAgentId());
					if(l_agent == null)
						throw new DataNotFoundException("Agent doesn't exist", Agent.class);
					
					AgentCOA l_agentcoa = agentCOADAO.find(l_agent.getAccountset_id());
					if(l_agentcoa == null)
						throw new DataNotFoundException("Agent COA doesn't exist", AgentCOA.class);
					
					GeneralLedger _glDebit = new GeneralLedger();
					_glDebit.setGlKey(l_glmgr.getGLKey());
					_glDebit.setSlip_ID(l_entity.getInvoiceCode() + "-DD");
					_glDebit.setSlipDate(l_entity.getInvoiceDate());
					_glDebit.setSystemDate(new Date());
					_glDebit.setCoaKey(l_branchCOA.getCurrentAccountID());
					_glDebit.setDepartmentKey(l_branchCOA.getDepartmentID());
					_glDebit.setCurrencyKey(l_entity.getL_detail().get(i).getCurrencyId());
					_glDebit.setCurrencyRateKey(l_currencyrate.getCurrencyRateId());
					_glDebit.setExchangeRate(l_currencyrate.getRate());
					_glDebit.setSourceAmount(l_entity.getL_detail().get(i).getAmount());
					
					if(l_currencyrate.getRoper().equals("M"))
						_glDebit.setHomeAmount((l_entity.getL_detail().get(i).getAmount() * l_currencyrate.getRate()));
					else
						_glDebit.setHomeAmount((l_entity.getL_detail().get(i).getAmount() / l_currencyrate.getRate()));
					
					_glDebit.setStatus("JDD");
					_glDebit.setSrNo((i + 1));
					_glDebit.setOffice_ID(l_entity.getBranchId());
					
					l_glmgr.createGL(_glDebit);
					
					GeneralLedger _glCredit = new GeneralLedger();
					_glCredit.setGlKey(l_glmgr.getGLKey());
					_glCredit.setSlip_ID(l_entity.getInvoiceCode() + "-DD");
					_glCredit.setSlipDate(l_entity.getInvoiceDate());
					_glCredit.setSystemDate(new Date());
					_glCredit.setCoaKey(l_agentcoa.getAccpayable());
					_glCredit.setDepartmentKey(l_agentcoa.getAccpayable_dept());
					_glCredit.setCurrencyKey(l_entity.getL_detail().get(i).getCurrencyId());
					_glCredit.setCurrencyRateKey(l_currencyrate.getCurrencyRateId());
					_glCredit.setExchangeRate(l_currencyrate.getRate());
					_glCredit.setSourceAmount(-l_entity.getL_detail().get(i).getAmount());
					
					if(l_currencyrate.getRoper().equals("M"))
						_glCredit.setHomeAmount(-(l_entity.getL_detail().get(i).getAmount() * l_currencyrate.getRate()));
					else
						_glCredit.setHomeAmount(-(l_entity.getL_detail().get(i).getAmount() / l_currencyrate.getRate()));
					
					_glCredit.setStatus("JDD");
					_glCredit.setSrNo((i + 1));
					_glCredit.setOffice_ID(l_entity.getBranchId());
					
					l_glmgr.createGL(_glCredit);
				
				}
			}
			
		}
	}
	
	//Report
	public JasperPrint prepareDefinePaymentTypeReport(DefinePaymentTypeRpt entity)throws Exception{
		openConnection();
		JasperPrint l_jsPrint = null;
		try{
			definePaymentTypeDAO = RemittanceDAOFactory.getDefinePaymentTypeDAO(getConnection());
			JRResultSetDataSource l_jrRS = new JRResultSetDataSource(definePaymentTypeDAO.prepareDefinePaymentTypeReport(entity));
			JasperReport l_jsRpt = null;
			if(entity.getStatus().equalsIgnoreCase("summary"))
				l_jsRpt = (JasperReport) JRLoader.loadObjectFromFile(CommonSingleton.ABSOLUTE_PATH+"reports/CashRemittanceSummaryRpt.jasper");
			else
				l_jsRpt = (JasperReport) JRLoader.loadObjectFromFile(CommonSingleton.ABSOLUTE_PATH+"reports/CashRemittanceDetailRpt.jasper");
			HashMap<String, Object> l_params = new HashMap<String, Object>();
			l_params.put("company", "mbc");
			l_jsPrint = JasperFillManager.fillReport(l_jsRpt, l_params, l_jrRS);
		}catch (JRException je){
			throw je;
		}catch (Exception e){
			throw e;
		}finally{
			closeConnection();
		}
		return l_jsPrint;
	}
	
	public String getResultSetForExcelDPT(DefinePaymentTypeRpt entity, String path)throws Exception {
		openConnection();
		try {
			
			GenerateExcel excelexport = new GenerateExcel("Cash Remittance (A, B, S, O, W)");
			
			excelexport.putData(String.class, new java.sql.Timestamp(entity.getFromdate().getTime()).toString());
			excelexport.putData(String.class, new java.sql.Timestamp(entity.getTodate().getTime()).toString());
			excelexport.putData(String.class, entity.getFrominvoiceID());
			excelexport.putData(String.class, entity.getToinvoiceID());
			excelexport.putData(String.class, entity.getFrombranchID());
			excelexport.putData(String.class, entity.getTobranchID());
			excelexport.putData(String.class, entity.getFromagentID());
			excelexport.putData(String.class, entity.getToagentID());
			excelexport.putData(String.class, entity.getFromtypeID());
			excelexport.putData(String.class, entity.getTotypeID());
			excelexport.putData(String.class, entity.getFromreceiptNo());
			excelexport.putData(String.class, entity.getToreceiptNo());
			excelexport.putData(String.class, entity.getFromuserID());
			excelexport.putData(String.class, entity.getTouserID());
			excelexport.putData(String.class, entity.getFromcurrencyID());
			excelexport.putData(String.class, entity.getTocurrencyID());
			excelexport.putData(String.class, entity.getExtrastatus());
			excelexport.putData(String.class, entity.getStatus());
			excelexport.putData(String.class, entity.getOrderBy());
			excelexport.putData(Long.class, entity.getBranchID());
			
			excelexport.putField("No");
			excelexport.putField("AgentCode", "Agent Code");
			excelexport.putField("name", "Agent Name");
			excelexport.putField("ReceiptNo", "Receipt No");
			excelexport.putField("Sender");
			excelexport.putField("Amount");
			excelexport.putField("Receiver");
			excelexport.putField("Address");
			excelexport.putField("CurrencyCode", "Currency Code");
			excelexport.putField("ExtraFees", "Extra Fees");
			excelexport.putField("username", "User Name");
			
			if(entity.getStatus().equals("detail")) {
				excelexport.putField("TypeCode", "Type Code");
				excelexport.putField("BranchCode", "Branch Code");
			}
			
			return excelexport.generate(getConnection(), true, "[dbo].[sp_cr_DefinePaymentTypeRpt]", path);
			
			/*definePaymentTypeDAO = RemittanceDAOFactory.getDefinePaymentTypeDAO(getConnection());
			ResultSet l_rs = definePaymentTypeDAO.prepareDefinePaymentTypeReport(entity);
			
			int columncount = l_rs.getMetaData().getColumnCount();
			
			HSSFWorkbook l_workbook = new HSSFWorkbook();
			HSSFSheet l_Sheet = l_workbook.createSheet("data");
			
			int l_rowNum = 0, l_cellNum = 0;
			HSSFRow l_headerRow = l_Sheet.createRow(l_rowNum++);
			
			HSSFCell l_cellNo = l_headerRow.createCell(l_cellNum++);
			l_cellNo.setCellValue("No.");
			
			HSSFCell l_cellAgentID = l_headerRow.createCell(l_cellNum++);
			l_cellAgentID.setCellValue("Agent ID");
			
			HSSFCell l_cellAgentName = l_headerRow.createCell(l_cellNum++);
			l_cellAgentName.setCellValue("Agent Name");
			
			HSSFCell l_cellReceiptNo = l_headerRow.createCell(l_cellNum++);
			l_cellReceiptNo.setCellValue("Receipt No");
			
			HSSFCell l_cellSender = l_headerRow.createCell(l_cellNum++);
			l_cellSender.setCellValue("Sender");
			
			HSSFCell l_cellAmount = l_headerRow.createCell(l_cellNum++);
			l_cellAmount.setCellValue("Amount");
			
			HSSFCell l_cellReceiver = l_headerRow.createCell(l_cellNum++);
			l_cellReceiver.setCellValue("Receiver");
			
			HSSFCell l_cellAddress = l_headerRow.createCell(l_cellNum++);
			l_cellAddress.setCellValue("Address");
			
			HSSFCell l_cellCurrenyCode = l_headerRow.createCell(l_cellNum++);
			l_cellCurrenyCode.setCellValue("Currency Code");
			
			HSSFCell l_cellExtraFees = l_headerRow.createCell(l_cellNum++);
			l_cellExtraFees.setCellValue("Extra Fees");
			
			HSSFCell l_cellUserName = l_headerRow.createCell(l_cellNum++);
			l_cellUserName.setCellValue("User Name");
			
			HSSFCell l_cellTypeCode;
			HSSFCell l_cellBrandCode;
			if(entity.getStatus().equals("detail")) {
				l_cellTypeCode = l_headerRow.createCell(l_cellNum++);
				l_cellTypeCode.setCellValue("Type Code");
			
				l_cellBrandCode = l_headerRow.createCell(l_cellNum++);
				l_cellBrandCode.setCellValue("Brand Code");
			}
			
			while(l_rs.next()) {
				HSSFRow l_row = l_Sheet.createRow(l_rowNum++);
				l_cellNum = 0;
				
				for(int i = 0; i < columncount; i++) {
					
					String columnName = l_rs.getMetaData().getColumnName(i + 1);
					
					if(columnName.equals("No")) {
						l_cellNo = l_row.createCell(l_cellNum++);
						l_cellNo.setCellValue(l_rs.getString(i + 1));
					} else if(columnName.equals("AgentCode")) {
						l_cellAgentID = l_row.createCell(l_cellNum++);
						l_cellAgentID.setCellValue(l_rs.getString(i + 1));
					} else if(columnName.equals("name")) {				
						l_cellAgentName = l_row.createCell(l_cellNum++);
						l_cellAgentName.setCellValue(l_rs.getString(i + 1));
					} else if(columnName.equals("ReceiptNo")) {
						l_cellReceiptNo = l_row.createCell(l_cellNum++);
						l_cellReceiptNo.setCellValue(l_rs.getString(i + 1));
					} else if(columnName.equals("Sender")) {
						l_cellSender = l_row.createCell(l_cellNum++);
						l_cellSender.setCellValue(l_rs.getString(i + 1));
					} else if(columnName.equals("Amount")) {
						l_cellAmount = l_row.createCell(l_cellNum++);
						l_cellAmount.setCellValue(l_rs.getString(i + 1));
					} else if(columnName.equals("Receiver")) {
						l_cellReceiver = l_row.createCell(l_cellNum++);
						l_cellReceiver.setCellValue(l_rs.getString(i + 1));
					} else if(columnName.equals("Address")) {
						l_cellAddress = l_row.createCell(l_cellNum++);
						l_cellAddress.setCellValue(l_rs.getString(i + 1));
					} else if(columnName.equals("CurrencyCode")) {
						l_cellCurrenyCode = l_row.createCell(l_cellNum++);
						l_cellCurrenyCode.setCellValue(l_rs.getString(i + 1));
					} else if(columnName.equals("ExtraFees")) {
						l_cellExtraFees = l_row.createCell(l_cellNum++);
						l_cellExtraFees.setCellValue(l_rs.getString(i + 1));
					} else if(columnName.equals("username")) {
						l_cellUserName = l_row.createCell(l_cellNum++);
						l_cellUserName.setCellValue(l_rs.getString(i + 1));
					}
					
					if(entity.getStatus().equals("detail")) {
						if(columnName.equals("TypeCode")) {
							l_cellTypeCode = l_row.createCell(l_cellNum++);
							l_cellTypeCode.setCellValue(l_rs.getString(i + 1));
						} else if(columnName.equals("BranchCode")) {
							l_cellBrandCode = l_row.createCell(l_cellNum++);
							l_cellBrandCode.setCellValue(l_rs.getString(i + 1));
						}
					}
					
				}
			}
			
			return l_workbook;*/
		}  catch(Exception e) {
			throw e;
		} finally {
			closeConnection();
		}
	}
	
	//Print
		public JasperPrint prepareDefinePaymentTypePrint(DefinePaymentPrint entity, String[] myanmarNumberValues, String myanmarCurrencyName, String myanmarDecName, String... myanmarNumbers)throws Exception{
			openConnection();
			JasperPrint l_jsPrint = null;
			try{
				definePaymentTypeDAO = RemittanceDAOFactory.getDefinePaymentTypeDAO(getConnection());
				
				ResultSet l_rs = definePaymentTypeDAO.prepareDefinePaymentTypePrint(entity);
				
				String amountinword = ""; 
				try {
					ResultSet l_temp = definePaymentTypeDAO.prepareDefinePaymentTypePrint(entity);
					
					if(l_temp.next()) {
						double amount = l_temp.getDouble("Amount");
					
						amountinword = DefinePaymentTypeDAOImpls.amountInWordsMM(String.valueOf(amount), myanmarNumberValues, myanmarCurrencyName, myanmarDecName, myanmarNumbers);
					}
				} catch(Exception e) { }
				
				JRResultSetDataSource l_jrRS = new JRResultSetDataSource(l_rs);
				JasperReport l_jsRpt = null;
					l_jsRpt = (JasperReport) JRLoader.loadObjectFromFile(CommonSingleton.ABSOLUTE_PATH+"reports/DPPrint.jasper");
				HashMap<String, Object> l_params = new HashMap<String, Object>();
				l_params.put("company", "mbc");
				l_params.put("myanAmount", amountinword);
				l_params.put("symbol", entity.getSymbol());
				l_jsPrint = JasperFillManager.fillReport(l_jsRpt, l_params, l_jrRS);
			}catch (JRException je){
				throw je;
			}catch (Exception e){
				throw e;
			}finally{
				closeConnection();
			}
			return l_jsPrint;
		}
		
	public Agent searchAgent(String mode, Agent agent) throws Exception {
		openConnection();
		try {
			definePaymentTypeDetailDAO = RemittanceDAOFactory.getDefinePaymentTypeDetailDAO(getConnection());
			return definePaymentTypeDetailDAO.searchAgent(mode, agent);
		} catch(Exception e) { 
			throw e;
		}
		finally {
			closeConnection();
		}
	}
	
	public DefinePaymentTypeDetailDataList getReceiptNo(DefinePaymentTypeDetail entity)throws Exception{
		openConnection();
		try{
			definePaymentTypeDetailDAO = RemittanceDAOFactory.getDefinePaymentTypeDetailDAO(getConnection());
			return definePaymentTypeDetailDAO.getReceiptNo(entity);
		}catch(Exception e){
			throw e;
		}
		finally{
			closeConnection();
		}
	}
}
